using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using Musicman.Common;

namespace Musicman.DAL
{
    public static class RolesDAO
    {
        public static bool AssignRoleToUser(long userID, string role, DbTransaction transactionObj, SqlDatabase db)
        {
            string inserSQL = String.Format("INSERT INTO MUSICMAN.DBO.USERROLES(USERID, ROLE) VALUES ({0}, '{1}')", userID, role);
            bool res = Convert.ToBoolean(db.ExecuteNonQuery(transactionObj, CommandType.Text, inserSQL));
            return res;
        }

        public static string[] GetUserRoles(string email)
        {
            string selectSQL = String.Format(@"select ur1.role from musicman.dbo.users u1 
                                            join musicman.dbo.UserRoles ur1 on u1.personid = ur1.userID
                                 where u1.emailaddress = '{0}'", email);

            List<String> res = new List<String>();
			IDataReader reader = null;
			try
			{
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, selectSQL);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        string role = reader.GetString(0);
                        res.Add(role);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            if (res != null)
            {
                return res.ToArray();
            }
            else
            {
                return null;
            }
        }
    }
}
